﻿using NLog;
using Stock.Model.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Stock.Dao
{
    public class BaseDao
    {
        // 连接字符串
        private static string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Stock.mdb;";

        //声明

        private static readonly Logger logger = LogManager.GetCurrentClassLogger();
        #region 数据库操作

        public static DataTable GetDataTable(string sql)
        {
            OleDbConnection conn = null;
            OleDbDataAdapter adapter = null;
            DataSet dataSet = new DataSet();
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();

                adapter = new OleDbDataAdapter(sql, conn);
                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.Connection = conn;
                oleDbCommand.CommandText = sql;
                oleDbCommand.CommandType = CommandType.Text;
                adapter.SelectCommand = oleDbCommand;
                adapter.Fill(dataSet, "T_STOCK");
                conn.Close();
                oleDbCommand.Dispose();
            }
            catch (Exception ex)
            {
                logger.Error("BaseDao - GetDataTable:" + ex.Message);
                throw ex;
            }

            return dataSet.Tables["T_STOCK"];
        }

        public static List<T> GetList<T>(string sql)
        {
            DataTable dataTable = GetDataTable(sql);
            return DataTableToList<T>(dataTable);
        }

        public static T GetObject<T>(string sql)
        {
            DataTable dataTable = GetDataTable(sql);
            return DataTableToList<T>(dataTable).FirstOrDefault();
        }


        public static bool Insert(string sql)
        {
            int rows = 0;
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();

                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.Connection = conn;
                oleDbCommand.CommandText = sql;
                oleDbCommand.CommandType = CommandType.Text;

                rows = oleDbCommand.ExecuteNonQuery();
                conn.Close();
                oleDbCommand.Dispose();
            }
            catch (Exception ex)
            {
                logger.Error("===================================================================");
                logger.Error("BaseDao - Insert:" + ex.Message);
                logger.Error("BaseDao - Insert:" + sql);
                logger.Error("===================================================================");
                //throw ex;
            }
            return rows > 0 ? true : false;
        }

        public static bool Update(string sql)
        {
            int rows = 0;
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.Connection = conn;
                oleDbCommand.CommandText = sql;
                oleDbCommand.CommandType = CommandType.Text;

                rows = oleDbCommand.ExecuteNonQuery();
                conn.Close();
                oleDbCommand.Dispose();
            }
            catch (Exception ex)
            {
                logger.Error("===================================================================");
                logger.Error("BaseDao - Update:" + ex.Message);
                logger.Error("BaseDao - Update:" + sql);
                logger.Error("===================================================================");
            }
            return rows > 0 ? true : false;
        }



        public static bool Delete(string sql)
        {
            int rows = 0;
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();

                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.Connection = conn;
                oleDbCommand.CommandText = sql;
                oleDbCommand.CommandType = CommandType.Text;
                rows = oleDbCommand.ExecuteNonQuery();

                conn.Close();
                oleDbCommand.Dispose();
            }
            catch (Exception ex)
            {
                logger.Error("===================================================================");
                logger.Error("BaseDao - Delete:" + ex.Message);
                logger.Error("BaseDao - Delete:" + sql);
                logger.Error("===================================================================");
            }
            return rows > 0 ? true : false;
        }

        public static bool Insert<T>(T t) where T : class
        {
            Insert(GetInsertSQL<T>(t));
            return true;
        }

        public static bool Update<T>(T t)
        {
            string tableName = GetTableName<T>(t);
            if (string.IsNullOrEmpty(tableName))
                return false;
            string sql = string.Empty;
            string condition = string.Empty;
            string columns = string.Empty, values = string.Empty;
            sql = string.Format("UPDATE {0} SET ", tableName);
            PropertyInfo[] propinfo = t.GetType().GetProperties();

            foreach (PropertyInfo item in propinfo)
            {
                ColumnsAttribute columnsAttribute = (ColumnsAttribute)Attribute.GetCustomAttribute(item, typeof(ColumnsAttribute));
                if (columnsAttribute != null)//自定义列名                        
                    columns = "[" + columnsAttribute._FieldName + "]";
                else
                    columns = "[" + item.Name + "]";
                if (item.PropertyType == typeof(string))//自定义列名                             
                    values = "'" + item.GetValue(t, null) + "'";
                else if (item.PropertyType == typeof(DateTime))
                    values = "#" + item.GetValue(t, null) + "#";
                else
                    values = item.GetValue(t, null).ToString();

                PrimaryKeyAttribute primaryKeyAttribute = (PrimaryKeyAttribute)Attribute.GetCustomAttribute(item, typeof(PrimaryKeyAttribute));
                if (primaryKeyAttribute != null)//主键过滤
                {
                    condition = " WHERE " + (string.IsNullOrEmpty(primaryKeyAttribute._FieldName) ? item.Name : primaryKeyAttribute._FieldName);
                    condition += " = " + values;
                }
                else
                {
                    sql += columns + " = " + values + " ,";
                }
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += condition + ";";
            Update(sql);
            return true;

        }

        public static string GetInsertSQL<T>(T t) where T : class
        {
            if (t == null)
                return "";

            string columns = GetColmons(t);
            if (string.IsNullOrEmpty(columns))
                return "";

            string values = GetValues(t);
            if (string.IsNullOrEmpty(values))
                return "";

            string tableName = GetTableName<T>(t);
            if (string.IsNullOrEmpty(tableName))
                return "";

            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO " + tableName);
            sql.Append(" (" + columns + ")");
            sql.Append(" VALUES (" + values + ");");
            return sql.ToString();
        }

        /// <summary>
        /// 获取表名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        private static string GetTableName<T>(T t)
        {
            MemberInfo info = typeof(T); //通过反射得到MyClass类的信息
                                         //得到施加在MyClass类上的定制Attribute
            TableNameAttribute att = (TableNameAttribute)Attribute.GetCustomAttribute(info, typeof(TableNameAttribute));
            if (att != null || !string.IsNullOrEmpty(att._TableName))
                return att._TableName;
            else
                return t.GetType().Name;
        }

        /// <summary>
        /// 获得类型的列名
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string GetColmons<T>(T t)
        {
            string columns = string.Empty;
            if (t == null)
            {
                return string.Empty;
            }
            PropertyInfo[] propinfo = t.GetType().GetProperties();

            foreach (PropertyInfo item in propinfo)
            {
                PrimaryKeyAttribute primaryKeyAttribute = (PrimaryKeyAttribute)Attribute.GetCustomAttribute(item, typeof(PrimaryKeyAttribute));

                if (primaryKeyAttribute != null)//主键过滤
                    continue;
                ColumnsAttribute columnsAttribute = (ColumnsAttribute)Attribute.GetCustomAttribute(item, typeof(ColumnsAttribute));
                if (columnsAttribute != null)//自定义列名                        
                    columns += "[" + columnsAttribute._FieldName + "],";
                else
                    columns += "[" + item.Name + "],";
            }
            if (columns.Length > 0)
                columns = columns.Substring(0, columns.Length - 1);
            return columns;
        }

        /// <summary>
        /// 获得值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string GetValues<T>(T t)
        {
            if (t == null)
            {
                return string.Empty;
            }
            string values = string.Empty;
            PropertyInfo[] propinfo = t.GetType().GetProperties();
            foreach (PropertyInfo item in propinfo)
            {

                PrimaryKeyAttribute primaryKeyAttribute = (PrimaryKeyAttribute)Attribute.GetCustomAttribute(item, typeof(PrimaryKeyAttribute));
                if (primaryKeyAttribute != null)//主键过滤
                    continue;

                if (item.PropertyType == typeof(string))//自定义列名                        
                    values += "'" + item.GetValue(t, null) + "',";
                else if (item.PropertyType == typeof(DateTime))
                    values += "#" + item.GetValue(t, null) + "#,";
                else
                    values += item.GetValue(t, null) + ",";
            }
            if (values.Length > 0)
                values = values.Substring(0, values.Length - 1);
            return values;
            //return string.Join("],[", obj.GetType().GetProperties().Select(p => string.Format("'{0}'", p.GetValue(obj))).ToArray());
        }


        private static string GetPKColumn<T>(T t)
        {
            string columns = string.Empty;
            if (t == null)
            {
                return columns;
            }
            string values = string.Empty;
            PropertyInfo[] propinfo = t.GetType().GetProperties();
            foreach (PropertyInfo item in propinfo)
            {

                PrimaryKeyAttribute primaryKeyAttribute = (PrimaryKeyAttribute)Attribute.GetCustomAttribute(item, typeof(PrimaryKeyAttribute));
                if (primaryKeyAttribute != null)//主键
                {
                    ColumnsAttribute columnsAttribute = (ColumnsAttribute)Attribute.GetCustomAttribute(item, typeof(ColumnsAttribute));
                    if (columnsAttribute != null)//自定义列名                        
                        columns = "[" + columnsAttribute._FieldName + "]";
                    else
                        columns = "[" + item.Name + "]";
                    break;
                }
            }

            return columns;
        }

        private static string GetPKValue<T>(T t)
        {
            string values = string.Empty;
            if (t == null)
            {
                return values;
            }

            PropertyInfo[] propinfo = t.GetType().GetProperties();
            foreach (PropertyInfo item in propinfo)
            {

                PrimaryKeyAttribute primaryKeyAttribute = (PrimaryKeyAttribute)Attribute.GetCustomAttribute(item, typeof(PrimaryKeyAttribute));
                if (primaryKeyAttribute != null)//主键过滤
                {

                    if (item.PropertyType == typeof(string))//自定义列名
                    {
                        values = "'" + item.GetValue(t, null) + "'";
                    }
                    else if (item.PropertyType == typeof(DateTime))
                    {
                        values = "#" + item.GetValue(t, null) + "#";
                    }
                    else
                        values = item.GetValue(t, null) + "";
                    break;
                }

            }

            return values;
        }

        #endregion
        /// <summary>
        /// 返回代码列表
        /// </summary>
        /// <returns></returns>
        public static string GetCodes(bool isLower = true)
        {
            string result = string.Empty;
            string sql = "SELECT [CODE],[PREFIX] FROM [T_STOCK];";
            DataTable data = GetDataTable(sql);
            for (int i = 0; i < data.Rows.Count; i++)
            {
                if (isLower)
                    result += data.Rows[i][1].ToString().ToLower() + data.Rows[i][0] + ",";
                else
                    result += data.Rows[i][1].ToString().ToUpper() + data.Rows[i][0] + ",";
            }

            return result;
        }



        #region DataTable to List

        /// <summary>
        /// 类型枚举
        /// </summary>
        private enum ModelType
        {
            //值类型
            Struct,
            Enum,
            //引用类型
            String,
            Object,
            Else
        }



        private static ModelType GetModelType(Type modelType)
        {
            //值类型
            if (modelType.IsEnum)
            {
                return ModelType.Enum;
            }
            //值类型
            if (modelType.IsValueType)
            {
                return ModelType.Struct;
            }
            //引用类型 特殊类型处理
            if (modelType == typeof(string))
            {
                return ModelType.String;
            }
            //引用类型 特殊类型处理
            return modelType == typeof(object) ? ModelType.Object : ModelType.Else;
        }





        /// <summary>
        /// datatable转换为List<T>集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <returns></returns>
        protected static List<T> DataTableToList<T>(DataTable table)
        {
            var list = new List<T>();
            foreach (DataRow item in table.Rows)
            {
                list.Add(DataRowToModel<T>(item));
            }
            return list;
        }

        protected static T DataRowToModel<T>(DataRow row)
        {
            T model;
            var type = typeof(T);
            var modelType = GetModelType(type);
            switch (modelType)
            {
                //值类型
                case ModelType.Struct:
                    {
                        model = default(T);
                        if (row[0] != null)
                            model = (T)row[0];
                    }
                    break;
                //值类型
                case ModelType.Enum:
                    {
                        model = default(T);
                        if (row[0] != null)
                        {
                            var fiType = row[0].GetType();
                            if (fiType == typeof(int))
                            {
                                model = (T)row[0];
                            }
                            else if (fiType == typeof(string))
                            {
                                model = (T)Enum.Parse(typeof(T), row[0].ToString());
                            }
                        }
                    }
                    break;
                //引用类型 c#对string也当做值类型处理
                case ModelType.String:
                    {
                        model = default(T);
                        if (row[0] != null)
                            model = (T)row[0];
                    }
                    break;
                //引用类型 直接返回第一行第一列的值
                case ModelType.Object:
                    {
                        model = default(T);
                        if (row[0] != null)
                            model = (T)row[0];
                    }
                    break;
                //引用类型
                case ModelType.Else:
                    {
                        //引用类型 必须对泛型实例化
                        model = Activator.CreateInstance<T>();
                        //获取model中的属性
                        var modelPropertyInfos = type.GetProperties();
                        //遍历model每一个属性并赋值DataRow对应的列
                        foreach (var pi in modelPropertyInfos)
                        {
                            //获取属性名称
                            var name = pi.Name;
                            if (!row.Table.Columns.Contains(name) || row[name] == null) continue;
                            var piType = GetModelType(pi.PropertyType);
                            switch (piType)
                            {
                                case ModelType.Struct:
                                    {
                                        var value = Convert.ChangeType(row[name], pi.PropertyType);
                                        pi.SetValue(model, value, null);
                                    }
                                    break;
                                case ModelType.Enum:
                                    {
                                        var fiType = row[0].GetType();
                                        if (fiType == typeof(int))
                                        {
                                            pi.SetValue(model, row[name], null);
                                        }
                                        else if (fiType == typeof(string))
                                        {
                                            var value = (T)Enum.Parse(typeof(T), row[name].ToString());
                                            if (value != null)
                                                pi.SetValue(model, value, null);
                                        }
                                    }
                                    break;
                                case ModelType.String:
                                    {
                                        var value = Convert.ChangeType(row[name], pi.PropertyType);
                                        pi.SetValue(model, value, null);
                                    }
                                    break;
                                case ModelType.Object:
                                    {
                                        pi.SetValue(model, row[name], null);
                                    }
                                    break;
                                case ModelType.Else:
                                    throw new Exception("不支持该类型转换");
                                default:
                                    throw new Exception("未知类型");
                            }
                        }
                    }
                    break;
                default:
                    model = default(T);
                    break;
            }
            return model;
        }



        #endregion

    }
}
